Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure.

Syntax to create Stored Procedure

CREATE PROCEDURE ProcedureName

AS

Body of the Procedure

Example

create procedure sp1
as
begin
      select * from Doctors
end

Now, once the procedure is created we need to execute procedure.

Syntax to execute procedure

EXECUTE ProcedureName

Or

EXEC ProcedureName

Example

EXECUTE sp1

Syntax for modifying procedure

ALTER PROCEDURE ProcedureName

AS

Body of Procedure

Example

ALTER PROCEDURE sp1
AS
BEGIN
      SELECT * FROM Doctors WHERE ID>4
END

Syntax for deleting procedure

DROP PROCEDURE ProcedureName

Example

DROP PROCEDURE sp1

Syntax for passing arguments to procedure

CREATE PROCEDURE ProcedureName

@ParameterName DataType

AS

Body of the Procedure

Example

CREATE PROCEDURE SP2
@IDNo INT
AS
BEGIN
      SELECT * FROM Doctors where ID=@IDNo
END

Executing parameterize procedure

EXECUTE sp25

Benefits of Stored Procedures

• Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
• Reduced client/server traffic.If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction.Stored procedures can be used by multiple users and client programs.
• Enhanced security controls.You can grant users permission to execute a stored procedure independently of underlying table permissions.

Difference between Function and Stored Procedure 

• A function is a subprogram written to perform certain computations and return a single value.
• Functions must return a value (using the RETURN keyword), but for stored          procedures this is not compulsory.
• Stored procedures can use RETURN keyword but without any value being          passed.
• Functions could be used in SELECT statements, provided they don’t do any        data manipulation. However, procedures cannot be included In SELECT statements.
• A function can have only IN parameters, while stored procedures may                have OUT or INOUT parameters.
• A stored procedure can return multiple values using the OUT parameter or        return no value at all.